#!/usr/bin/perl
use CGI;
use DBI;
use HTML::Template;
use strict;

#my $dbh = DBI->connect( "DBI:mysql:database=chadphil_windmeadow", 'chadphil_nuke', 'xxxxxxx' ) || die "it broke";
my $dbh = DBI->connect( "DBI:mysql:database=school", 'root', '' ) || die "it broke";

my $form = new CGI;

if ( $form->param('op') eq 'show_data' ) {
    print CGI::header();
    print show_data();

}
elsif ( $form->param('op') eq 'school1' ) {
    print CGI::header();

    print _get_school_checkbox( $form->param('cty_dist') );
}
else {
    print CGI::header();
    print main();
}

sub show_data {
    my $form = new CGI;
    my @schooldata;
    my %districts;

    my @grade_levels = sort ( $form->param('grade_level') );
    my $color        = "#DDDDDD";
    my $cnt          = 0;
    foreach my $cty_dist_school ( $form->param('cty_dist_school') ) {
        my %hash;
        $districts{ _get_district($cty_dist_school) } = 1;
        my $first = 1;
        foreach my $grade_level (@grade_levels) {
            my $school =
              _get_school_score( $cty_dist_school, $grade_level,
                $form->param('content_area') );

            if ($school) {
                if ($first) {
                    $cnt++;
                    $first = 0;
                }
                $school->{'color'} = _get_color($cnt);
                $school->{'cnt'}   = $cnt;
                push @schooldata, $school;
            }
        }
    }

    foreach my $district ( keys %districts ) {
        $cnt++;
        foreach my $grade_level (@grade_levels) {

            my $school =
              _get_average_district_school_score( $district, $grade_level,
                $form->param('content_area') );
            if ($school) {
                $school->{'color'} = _get_color($cnt);
                push @schooldata, $school;
            }
        }
    }

    $cnt++;
    foreach my $grade_level (@grade_levels) {
        my $school =
          _get_average_school_score( $grade_level,
            $form->param('content_area') );
        if ($school) {
            $school->{'color'} = _get_color($cnt);
            push @schooldata, $school;
        }
    }

    my $template = HTML::Template->new(
        filename          => './show_data.html',
        die_on_bad_params => 0
    );
    $template->param(
        data         => \@schooldata,
        content_area => $form->param('content_area'),
    );
    return $template->output();
}

sub _get_color {
    my $cnt = shift;
    if ( $cnt % 2 ) {
        return "#66FF99";
    }
    else {
        return "#FFFFCC";
    }
}

sub main {
    my $template =
      HTML::Template->new( filename => './main.html', die_on_bad_params => 0 );
    my $grade_level_radio_box  = _get_grade_level_radio_box();
    my $content_area_radio_box = _get_content_area_radio_box();
    $template->param(
        cgi_name               => $ENV{'SCRIPT_URL'},
        grade_level_radio_box  => $grade_level_radio_box,
        school_checkbox        => _get_school_checkbox(),
        content_area_radio_box => $content_area_radio_box,
        district1              => _get_district_scrollbox(
            'cty_dist1', "javascript:school(cty_dist1.value, 'school1');"
        ),
        district2 => _get_district_scrollbox(
            'cty_dist2', "javascript:school(cty_dist2.value, 'school2');"
        ),
        district3 => _get_district_scrollbox(
            'cty_dist3', "javascript:school(cty_dist3.value, 'school3');"
        ),
        district4 => _get_district_scrollbox(
            'cty_dist4', "javascript:school(cty_dist4.value, 'school4');"
        ),
    );
    return $template->output();
}

sub _get_school_checkbox {
    my $cty_dist = shift || '';
    my $schools  = _get_school_list();
    my @values   = _get_school_values_list($cty_dist);

    return CGI::popup_menu(
        -name   => 'cty_dist_school',
        -values => \@values,
        -labels => $schools,
    );
}

sub _get_school_list {
    my %rows;
    my $sql = qq!
              select 
                      cty_dist_school, schlname, district_name
              from
                      map_data
              group by district_name, cty_dist_school, schlname
              order by district_name, schlname
      !;

    my $sth = $dbh->prepare($sql);
    $sth->execute;
    while ( my $ref = $sth->fetchrow_hashref() ) {
        $rows{ $ref->{'cty_dist_school'} } = $ref->{'schlname'};
    }
    return \%rows;
}

sub _get_school_values_list {
    my $cty_dist = shift || '';
    my @rows;
    my $where;
    if ($cty_dist) {
        $where = " where cty_dist = '$cty_dist' ";
    }
    my $sql = qq!
              select
                      cty_dist_school
              from
                      map_data
              $where
              group by district_name, cty_dist_school, schlname
              order by district_name, schlname
      !;
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    while ( my $ref = $sth->fetchrow_hashref() ) {
        push @rows, $ref->{'cty_dist_school'};
    }
    return @rows;
}

sub _get_content_area_radio_box {
    my $sql = qq!
       select
             content_area
       from
                      map_data
       group by content_area
     !;
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    my @content_area;
    while ( my $ref = $sth->fetchrow_hashref() ) {
        push @content_area, $ref->{'content_area'};
    }

    return CGI::radio_group(
        -name   => 'content_area',
        -values => \@content_area,
        -cols   => 1,
    );
}

sub _get_grade_level_radio_box {
    my $sql = qq!
       select
             grade_level 
       from
                      map_data
       group by grade_level 
     !;
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    my @grade_level;
    while ( my $ref = $sth->fetchrow_hashref() ) {
        push @grade_level, $ref->{'grade_level'};
    }

    return CGI::checkbox_group(
        -name    => 'grade_level',
        -values  => \@grade_level,
        -default => \@grade_level,
    );
}

sub _get_school_score {
    my $cty_dist_school = shift;
    my $grade_level     = shift;
    my $content_area    = shift;

    my $sql = qq!
          select
                 *
          from
              map_data
          where
              cty_dist_school = ? 
          and grade_level     = ? 
          and content_area    = ? 
     !;
    my $sth = $dbh->prepare($sql);
    $sth->execute( $cty_dist_school, $grade_level, $content_area );
    while ( my $ref = $sth->fetchrow_hashref() ) {
        return $ref;
    }
    return;
}

sub _get_average_school_score {
    my $grade_level  = shift;
    my $content_area = shift;

    my $sql = qq!
          select
                'State Average' as schlname,
                round( avg(accountable), 2) as accountable,
                round( avg(level_not_determined_pct), 2) as level_not_determined_pct,
                round( sum(reportable * below_basic_pct) / sum(reportable), 2) as below_basic_pct, 
                round( sum(reportable * basic_pct) / sum(reportable), 2) as basic_pct, 
                round( sum(reportable * proficient_pct) / sum(reportable), 2) as proficient_pct, 
                round( sum(reportable * advanced_pct) / sum(reportable), 2) as advanced_pct, 
                sum(reportable) as reportable,
                sum(accountable) as accountable 
          from
              map_data
          where
              grade_level     = ?
          and content_area    = ?
     !;
    my $sth = $dbh->prepare($sql);
    $sth->execute( $grade_level, $content_area );
    while ( my $ref = $sth->fetchrow_hashref() ) {
        $ref->{'grade_level'} = $grade_level;
        if ( $ref->{'basic_pct'} ) {
            return $ref;
        }
    }
}

sub _get_average_district_school_score {
    my $cty_dist     = shift;
    my $grade_level  = shift;
    my $content_area = shift;

    my $sql = qq!
          select
                district_name,
                round( sum(reportable * below_basic_pct) / sum(reportable), 2) as below_basic_pct, 
                round( sum(reportable * basic_pct) / sum(reportable), 2) as basic_pct, 
                round( sum(reportable * proficient_pct) / sum(reportable), 2) as proficient_pct, 
                round( sum(reportable * advanced_pct) / sum(reportable), 2) as advanced_pct, 
                sum(reportable) as reportable,
                sum(accountable) as accountable 
          from
              map_data
          where
              grade_level     = ?
          and content_area    = ?
          and cty_dist = ?
          group by district_name
     !;
    my $sth = $dbh->prepare($sql);
    $sth->execute( $grade_level, $content_area, $cty_dist );
    while ( my $ref = $sth->fetchrow_hashref() ) {
        $ref->{'schlname'}    = "District Avg: " . $ref->{'district_name'};
        $ref->{'grade_level'} = $grade_level;
        return $ref;
    }
}

sub _get_district {
    my $cty_dist_school = shift;
    my $sql             = qq!
        select
             cty_dist
        from
             map_data
        where
             cty_dist_school = ?
        group by cty_dist
        !;
    my $sth = $dbh->prepare($sql);
    $sth->execute($cty_dist_school);
    while ( my $ref = $sth->fetchrow_hashref() ) {
        return $ref->{'cty_dist'};
    }
}

sub _get_district_scrollbox {
    my $name    = shift;
    my $onclick = shift || '';
    my $sql     = qq!
          select
             cty_dist , district_name
        from
             map_data
        group by cty_dist , district_name
        order by district_name
         !;
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my @values = ('');
    my %labels;
    $labels{''} = 'Select School District';

    while ( my $ref = $sth->fetchrow_hashref() ) {
        push @values, $ref->{'cty_dist'};
        $labels{ $ref->{'cty_dist'} } = $ref->{'district_name'};
    }

    return CGI::popup_menu(
        -name     => $name,
        -values   => \@values,
        -labels   => \%labels,
        -default  => '',
        -onchange => $onclick
    );
}

sub _get_picked_schools {
    my $form    = new CGI;
    my @schools = $form->cookie('school');
    return @schools;
}
